using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;


namespace ASPNETVN.PORTAL.Components.Security
{
    public partial class User
    {
        #region Private members.

        protected int _ID;
        protected Guid _PortalID = Guid.Empty;
        protected string _Name = string.Empty;
        protected string _Password = string.Empty;
        protected string _Email = string.Empty;
        protected bool _Gender;
        protected string _Address = string.Empty;
        protected string _City = string.Empty;
        protected string _Country = string.Empty;
        protected string _Phone = string.Empty;
        protected string _AvatarImagePath = string.Empty;
        protected string _Status = string.Empty;
        protected DateTime _JoinedTime = new DateTime(1753, 1, 1);

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Properties.

        public int ID
        {
            set { this._ID = value; }
            get { return this._ID; }
        }

        public Guid PortalID
        {
            set { this._PortalID = value; }
            get { return this._PortalID; }
        }

        public string Name
        {
            set { this._Name = value; }
            get { return this._Name; }
        }

        public string Password
        {
            set { this._Password = value; }
            get { return this._Password; }
        }

        public string Email
        {
            set { this._Email = value; }
            get { return this._Email; }
        }

        public bool Gender
        {
            set { this._Gender = value; }
            get { return this._Gender; }
        }

        public string Address
        {
            set { this._Address = value; }
            get { return this._Address; }
        }

        public string City
        {
            set { this._City = value; }
            get { return this._City; }
        }

        public string Country
        {
            set { this._Country = value; }
            get { return this._Country; }
        }

        public string Phone
        {
            set { this._Phone = value; }
            get { return this._Phone; }
        }

        public string AvatarImagePath
        {
            set { this._AvatarImagePath = value; }
            get { return this._AvatarImagePath; }
        }

        public string Status
        {
            set { this._Status = value; }
            get { return this._Status; }
        }

        public DateTime JoinedTime
        {
            set { this._JoinedTime = value; }
            get { return this._JoinedTime; }
        }


        #endregion

        //---------------------------------------------------------------------------------------------

        #region Select methods.

        public static User Load(int iD)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Load]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, iD);
            User entity = null;
            IDataReader reader = db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                entity = new User();
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("PortalID"))) entity.PortalID = reader.GetGuid(reader.GetOrdinal("PortalID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Name"))) entity.Name = reader.GetString(reader.GetOrdinal("Name"));
                if (!reader.IsDBNull(reader.GetOrdinal("Password"))) entity.Password = reader.GetString(reader.GetOrdinal("Password"));
                if (!reader.IsDBNull(reader.GetOrdinal("Email"))) entity.Email = reader.GetString(reader.GetOrdinal("Email"));
                if (!reader.IsDBNull(reader.GetOrdinal("Gender"))) entity.Gender = reader.GetBoolean(reader.GetOrdinal("Gender"));
                if (!reader.IsDBNull(reader.GetOrdinal("Address"))) entity.Address = reader.GetString(reader.GetOrdinal("Address"));
                if (!reader.IsDBNull(reader.GetOrdinal("City"))) entity.City = reader.GetString(reader.GetOrdinal("City"));
                if (!reader.IsDBNull(reader.GetOrdinal("Country"))) entity.Country = reader.GetString(reader.GetOrdinal("Country"));
                if (!reader.IsDBNull(reader.GetOrdinal("Phone"))) entity.Phone = reader.GetString(reader.GetOrdinal("Phone"));
                if (!reader.IsDBNull(reader.GetOrdinal("AvatarImagePath"))) entity.AvatarImagePath = reader.GetString(reader.GetOrdinal("AvatarImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("JoinedTime"))) entity.JoinedTime = reader.GetDateTime(reader.GetOrdinal("JoinedTime"));
            }
            reader.Close();
            return entity;
        }

        //---------------------------------------------------------------------------------------------
        public static IList<User> SelectCollectionAll()
        {
            IList<User> collection = new List<User>();
            IDataReader reader = SelectReaderAll();
            while (reader.Read())
            {
                User entity = new User();

                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("PortalID"))) entity.PortalID = reader.GetGuid(reader.GetOrdinal("PortalID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Name"))) entity.Name = reader.GetString(reader.GetOrdinal("Name"));
                if (!reader.IsDBNull(reader.GetOrdinal("Password"))) entity.Password = reader.GetString(reader.GetOrdinal("Password"));
                if (!reader.IsDBNull(reader.GetOrdinal("Email"))) entity.Email = reader.GetString(reader.GetOrdinal("Email"));
                if (!reader.IsDBNull(reader.GetOrdinal("Gender"))) entity.Gender = reader.GetBoolean(reader.GetOrdinal("Gender"));
                if (!reader.IsDBNull(reader.GetOrdinal("Address"))) entity.Address = reader.GetString(reader.GetOrdinal("Address"));
                if (!reader.IsDBNull(reader.GetOrdinal("City"))) entity.City = reader.GetString(reader.GetOrdinal("City"));
                if (!reader.IsDBNull(reader.GetOrdinal("Country"))) entity.Country = reader.GetString(reader.GetOrdinal("Country"));
                if (!reader.IsDBNull(reader.GetOrdinal("Phone"))) entity.Phone = reader.GetString(reader.GetOrdinal("Phone"));
                if (!reader.IsDBNull(reader.GetOrdinal("AvatarImagePath"))) entity.AvatarImagePath = reader.GetString(reader.GetOrdinal("AvatarImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("JoinedTime"))) entity.JoinedTime = reader.GetDateTime(reader.GetOrdinal("JoinedTime"));
                collection.Add(entity);
            }

            reader.Close();
            return collection;
        }

        //---------------------------------------------------------------------------------------------

        public static IList<User> SelectCollectionDynamic(string whereCondition, string orderByExpression)
        {
            IList<User> collection = new List<User>();

            IDataReader reader = SelectReaderDynamic(whereCondition, orderByExpression);
            while (reader.Read())
            {
                User entity = new User();

                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("PortalID"))) entity.PortalID = reader.GetGuid(reader.GetOrdinal("PortalID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Name"))) entity.Name = reader.GetString(reader.GetOrdinal("Name"));
                if (!reader.IsDBNull(reader.GetOrdinal("Password"))) entity.Password = reader.GetString(reader.GetOrdinal("Password"));
                if (!reader.IsDBNull(reader.GetOrdinal("Email"))) entity.Email = reader.GetString(reader.GetOrdinal("Email"));
                if (!reader.IsDBNull(reader.GetOrdinal("Gender"))) entity.Gender = reader.GetBoolean(reader.GetOrdinal("Gender"));
                if (!reader.IsDBNull(reader.GetOrdinal("Address"))) entity.Address = reader.GetString(reader.GetOrdinal("Address"));
                if (!reader.IsDBNull(reader.GetOrdinal("City"))) entity.City = reader.GetString(reader.GetOrdinal("City"));
                if (!reader.IsDBNull(reader.GetOrdinal("Country"))) entity.Country = reader.GetString(reader.GetOrdinal("Country"));
                if (!reader.IsDBNull(reader.GetOrdinal("Phone"))) entity.Phone = reader.GetString(reader.GetOrdinal("Phone"));
                if (!reader.IsDBNull(reader.GetOrdinal("AvatarImagePath"))) entity.AvatarImagePath = reader.GetString(reader.GetOrdinal("AvatarImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("JoinedTime"))) entity.JoinedTime = reader.GetDateTime(reader.GetOrdinal("JoinedTime"));
                collection.Add(entity);
            }

            reader.Close();
            return collection;
        }

        //---------------------------------------------------------------------------------------------

        // Select by foreign key return collection		


        public static DataSet SelectAll()
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);


            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static DataSet SelectDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderAll()
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        // Select by foreign key return collection		

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert methods.

        public static int InsertUser(Guid portalID, string name, string password, string email, bool gender, string address, string city, string country, string phone, string avatarImagePath, string status, DateTime joinedTime)
        {
            User entity = new User();
            entity.PortalID = portalID;
            entity.Name = name;
            entity.Password = password;
            entity.Email = email;
            entity.Gender = gender;
            entity.Address = address;
            entity.City = city;
            entity.Country = country;
            entity.Phone = phone;
            entity.AvatarImagePath = avatarImagePath;
            entity.Status = status;
            entity.JoinedTime = joinedTime;
            return entity.Insert();
        }

        public int Insert()
        {
            return this.Insert(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Insert(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Insert]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddOutParameter(dbCommand, "@ID", SqlDbType.Int, 4);
            db.AddInParameter(dbCommand, "@PortalID", SqlDbType.UniqueIdentifier, PortalID);
            db.AddInParameter(dbCommand, "@Name", SqlDbType.NVarChar, Name);
            db.AddInParameter(dbCommand, "@Password", SqlDbType.NVarChar, Password);
            db.AddInParameter(dbCommand, "@Email", SqlDbType.NVarChar, Email);
            db.AddInParameter(dbCommand, "@Gender", SqlDbType.Bit, Gender);
            db.AddInParameter(dbCommand, "@Address", SqlDbType.NVarChar, Address);
            db.AddInParameter(dbCommand, "@City", SqlDbType.NVarChar, City);
            db.AddInParameter(dbCommand, "@Country", SqlDbType.VarChar, Country);
            db.AddInParameter(dbCommand, "@Phone", SqlDbType.NVarChar, Phone);
            db.AddInParameter(dbCommand, "@AvatarImagePath", SqlDbType.NVarChar, AvatarImagePath);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@JoinedTime", SqlDbType.DateTime, JoinedTime.Year <= 1753 ? DBNull.Value : (object)JoinedTime);

            if (transaction != null)
            {
                db.ExecuteNonQuery(dbCommand, transaction);
                ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return ID;
            }
            else
            {
                db.ExecuteNonQuery(dbCommand);
                ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return ID;
            }
        }

        //---------------------------------------------------------------------------------------------
        public static bool InsertCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.Insert(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at InsertCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert / Update methods.

        public static int InsertUpdateUser(int iD, Guid portalID, string name, string password, string email, bool gender, string address, string city, string country, string phone, string avatarImagePath, string status, DateTime joinedTime)
        {
            User entity = new User();
            entity.PortalID = portalID;
            entity.Name = name;
            entity.Password = password;
            entity.Email = email;
            entity.Gender = gender;
            entity.Address = address;
            entity.City = city;
            entity.Country = country;
            entity.Phone = phone;
            entity.AvatarImagePath = avatarImagePath;
            entity.Status = status;
            entity.JoinedTime = joinedTime;
            return entity.InsertUpdate();
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdate()
        {
            return this.InsertUpdate(null);
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdate(SqlTransaction transaction)
        {
            const string spName = "p_SYSTEM_User_InsertUpdate";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);
            db.AddInParameter(dbCommand, "@PortalID", SqlDbType.UniqueIdentifier, PortalID);
            db.AddInParameter(dbCommand, "@Name", SqlDbType.NVarChar, Name);
            db.AddInParameter(dbCommand, "@Password", SqlDbType.NVarChar, Password);
            db.AddInParameter(dbCommand, "@Email", SqlDbType.NVarChar, Email);
            db.AddInParameter(dbCommand, "@Gender", SqlDbType.Bit, Gender);
            db.AddInParameter(dbCommand, "@Address", SqlDbType.NVarChar, Address);
            db.AddInParameter(dbCommand, "@City", SqlDbType.NVarChar, City);
            db.AddInParameter(dbCommand, "@Country", SqlDbType.VarChar, Country);
            db.AddInParameter(dbCommand, "@Phone", SqlDbType.NVarChar, Phone);
            db.AddInParameter(dbCommand, "@AvatarImagePath", SqlDbType.NVarChar, AvatarImagePath);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@JoinedTime", SqlDbType.DateTime, JoinedTime.Year == 1753 ? DBNull.Value : (object)JoinedTime);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------
        public static bool InsertUpdateCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.InsertUpdate(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at InsertUpdateCollection method: " + ex.Message);

                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }
        #endregion

        //---------------------------------------------------------------------------------------------

        #region Update methods.

        public static int UpdateUser(int iD, Guid portalID, string name, string password, string email, bool gender, string address, string city, string country, string phone, string avatarImagePath, string status, DateTime joinedTime)
        {
            User entity = new User();
            entity.ID = iD;
            entity.PortalID = portalID;
            entity.Name = name;
            entity.Password = password;
            entity.Email = email;
            entity.Gender = gender;
            entity.Address = address;
            entity.City = city;
            entity.Country = country;
            entity.Phone = phone;
            entity.AvatarImagePath = avatarImagePath;
            entity.Status = status;
            entity.JoinedTime = joinedTime;
            return entity.Update();
        }

        //---------------------------------------------------------------------------------------------

        public int Update()
        {
            return this.Update(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Update(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Update]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);
            db.AddInParameter(dbCommand, "@PortalID", SqlDbType.UniqueIdentifier, PortalID);
            db.AddInParameter(dbCommand, "@Name", SqlDbType.NVarChar, Name);
            db.AddInParameter(dbCommand, "@Password", SqlDbType.NVarChar, Password);
            db.AddInParameter(dbCommand, "@Email", SqlDbType.NVarChar, Email);
            db.AddInParameter(dbCommand, "@Gender", SqlDbType.Bit, Gender);
            db.AddInParameter(dbCommand, "@Address", SqlDbType.NVarChar, Address);
            db.AddInParameter(dbCommand, "@City", SqlDbType.NVarChar, City);
            db.AddInParameter(dbCommand, "@Country", SqlDbType.VarChar, Country);
            db.AddInParameter(dbCommand, "@Phone", SqlDbType.NVarChar, Phone);
            db.AddInParameter(dbCommand, "@AvatarImagePath", SqlDbType.NVarChar, AvatarImagePath);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@JoinedTime", SqlDbType.DateTime, JoinedTime.Year == 1753 ? DBNull.Value : (object)JoinedTime);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------
        public static bool UpdateCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.Update(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at UpdateCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Delete methods.

        public static int DeleteUser(int iD)
        {
            User entity = new User();
            entity.ID = iD;
            return entity.Delete();
        }

        public int Delete()
        {
            return this.Delete(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Delete(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Delete]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static bool DeleteCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.Delete(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at DeleteCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }
        #endregion
    }
}